﻿<%@ WebHandler Language="C#" Class="IncomeDetails" %>

using System;
using System.Web;
using Chenxin.Lib.Utility;
using System.Text;

public class IncomeDetails : AjaxHandlerBase
{
    public IncomeDetails()
    {
        //导出
        htTypePermit.Add("export", Constants.Permit_IncomeDetails_Export);
        htTypePermit.Add("exportall", Constants.Permit_IncomeDetails_Export);
    }
    override public void RequestProcess(HttpContext context)
    {
        try
        {
            context.Response.ContentType = "text/plain";
            context.Response.AddHeader("pragma", "no-cache");
            context.Response.AddHeader("cache-control", "");
            context.Response.CacheControl = "no-cache";

            string strOp = context.Request["op"];
            string strRet = string.Empty;
            switch (strOp.ToLower())
            {
                case "getlist":
                    GetList(context);
                    break;
                case "exportall":
                    SearchIncomeDetailsExport(context);
                    break;
                default:
                    break;
            }
        }
        catch (Exception ex)
        {
            context.Response.Write(retExecResult(Chenxin.Lib.Utility.Constants.ExecResult.Error, ex.Message, ""));
        }
    }

    /// <summary>
    /// 导出全部
    /// </summary>
    /// <param name="context"></param>
    private void SearchIncomeDetailsExport(HttpContext context)
    {
        string year = "";
        string month = "";

        if (!string.IsNullOrEmpty(context.Request["ActualDate"]))
        {
            string[] list = context.Request["ActualDate"].Split('-');
            year = list[0];
            month = list[1].TrimStart('0');
        }
        else
        {
            year = DateTime.Now.Year.ToString();
            month = DateTime.Now.Month.ToString();
        }

        //查询条件
        System.Text.StringBuilder sb = new System.Text.StringBuilder();

        if (CurrentUserInfo.ContainsPermit(Constants.Permit_IncomeDetails_Search_All))
        {
            sb.Append(" (1=1) ");
        }
        else if (CurrentUserInfo.ContainsPermit(Constants.Permit_IncomeDetails_Search_City))
        {
            sb.AppendFormat(" (DepartId in (Select DepartId from v_Hr_DepartmentInfo WHERE cityid='{0}')) ", CurrentUserInfo.CityId.ToString());
        }
        else if (CurrentUserInfo.ContainsPermit(Constants.Permit_IncomeDetails_Search_Company))
        {
            sb.AppendFormat(" (DepartId in (select DepartId from v_Hr_DepartmentInfo WHERE companyid='{0}')) ", CurrentUserInfo.CompanyId.ToString());
        }
        else if (CurrentUserInfo.ContainsPermit(Constants.Permit_IncomeDetails_Search_Depart))
        {
            sb.Append(" (EXISTS(");
            sb.AppendFormat("Select 1 from t_Business_EmpSetting M WHERE v_Report_Commission_FinanceSumInfo.DepartId=M.DepartId And M.Userid= '{0}' and SearchIncomeDetails=1", CurrentUserInfo.UserId.ToString());
            sb.Append(")) ");
        }
        else
            sb.AppendFormat(" (EmployeeId = '{0}') ", CurrentUserInfo.UserId);



        sb.AppendFormat(" and payyear='{0}' and paymonth ='{1}' ", year, month);

        //点数门店；合同所属
        if (context.Request["type"] == "10")
        {
            //4级
            Guid city = (string.IsNullOrEmpty(context.Request["cityid"])) ? Guid.Empty : new Guid(context.Request["cityid"]);
            Guid company = (string.IsNullOrEmpty(context.Request["companyid"])) ? Guid.Empty : new Guid(context.Request["companyid"]);
            Guid businessdistrict = (string.IsNullOrEmpty(context.Request["areaid"])) ? Guid.Empty : new Guid(context.Request["areaid"]);
            Guid departs = (string.IsNullOrEmpty(context.Request["departid"])) ? Guid.Empty : new Guid(context.Request["departid"]);
            Guid userId = (string.IsNullOrEmpty(context.Request["UserId"])) ? Guid.Empty : new Guid(context.Request["UserId"]);

            if (userId != Guid.Empty)
                sb.AppendFormat(" AND (EmployeeId = '{0}') ", userId);
            else if (departs != Guid.Empty)
            {
                sb.AppendFormat(" and DepartId = '{0}'", departs);
            }
            else if (businessdistrict != Guid.Empty)
            {
                Chenxin.Lib.Hr.Entity.Hr_DepartmentInfo item = null;
                string strCachkey = string.Format(Chenxin.Lib.Cache.CacheDataType.GLOBAL_DEPARTMENT, businessdistrict);
                Chenxin.Lib.Cache.CacheService cs = new Chenxin.Lib.Cache.CacheService(Chenxin.Lib.Cache.CacheConn.xCacheConn.GLOBAL);
                item = cs.GetObject<Chenxin.Lib.Hr.Entity.Hr_DepartmentInfo>(strCachkey);
                if (!cs.Hit)
                {
                    item = Chenxin.Lib.Hr.DAL.Hr_DepartmentInfo.GetModel(businessdistrict);
                    if (item != null) cs.Set(strCachkey, item, DateTime.Now.AddDays(1));
                }

                if (item == null)
                {
                    context.Response.Write(retExecResult(Chenxin.Lib.Utility.Constants.ExecResult.Error, "获取商圈或分类数据丢失", ""));
                }
                sb.AppendFormat(" and (DepartId in (Select DepartId from v_Hr_DepartmentInfo where DepartSrc like '{0}%'))", item.DepartSrc);
            }
            else if (company != Guid.Empty)
            {
                sb.AppendFormat(" and (DepartId in (select DepartId from v_Hr_DepartmentInfo WHERE companyid='{0}')) ", company);
            }
            else if (city != Guid.Empty)
            {
                sb.AppendFormat(" and (DepartId in (Select DepartId from v_Hr_DepartmentInfo WHERE cityid='{0}')) ", city);
            }
        }
        else if (context.Request["type"] == "20")
        {
            Guid company = (string.IsNullOrEmpty(context.Request["companyid"])) ? Guid.Empty : new Guid(context.Request["companyid"]);
            if (company != Guid.Empty)
            {
                sb.AppendFormat(" and (BargainCompanyId='{0}') ", company);
            }
        }

        //关键字
        if (!string.IsNullOrEmpty(context.Request["Keyword"]))
            sb.AppendFormat(" and (ContractCode like '%{0}%' or HouseAddress like '%{0}%')", context.Request["Keyword"]);

        //本期前期
        if (!string.IsNullOrEmpty(context.Request["datebelong"]))
        {
            //本期
            if (context.Request["datebelong"] == "10")
                sb.AppendFormat(" and ArchivementYear='{0}' and ArchivementMonth ='{1}' ", year, month);
            //前期
            else
                sb.AppendFormat(" and ((ArchivementYear='{0}' and ArchivementMonth <'{1}') or (ArchivementYear<'{0}')) ", year, month);
        }


        //int strPageIndex = (string.IsNullOrEmpty(context.Request["pageindex"])) ? 1 : int.Parse(context.Request["pageindex"]) + 1;//页码
        ////页显示行数
        //int strPageSize = (string.IsNullOrEmpty(context.Request["pagesize"])) ? 30 : int.Parse(context.Request["pagesize"]);//页记录数
        //欲选择字段列表 
        //string FieldList = "DepartId,business,departname,ContractCode,ContractDate,HouseAddress,Employee,typetime,realget,point,zget,fget,BargainCompanyName";


        //所要查询的表名
        string TableName = "v_Report_Commission_FinanceSumInfo";
        //排序
        string strOrderField = " DepartId desc";
        //子查询欲选择字段列表
        string TableFieldList = "DepartId,dbo.FN_GetBussinessByDepartID(DepartId) as business,dbo.FN_GetDepartNameByDepartID(DepartId) as departname,"
                    + string.Format("ContractCode,REPLACE(CONVERT(Varchar(10),ContractDate,111),'/','-') As ContractDate,HouseAddress,Employee,(case when (ArchivementYear='{0}' and ArchivementMonth ='{1}') then '10' ", year, month)
                    + string.Format("when ((ArchivementYear='{0}' and ArchivementMonth <'{1}') or (ArchivementYear<'{0}')) then '20' ", year, month)
                    + "end) as typetime,PayMoney*InOrOut as realget,point,"
                    + "case when (CAST(PayMoney*InOrOut * Point / 10 AS Decimal(18 , 2))>=0) then CAST(PayMoney*InOrOut * Point / 10 AS Decimal(18 , 2)) end as zget,"
                    + "case when (CAST(PayMoney*InOrOut * Point / 10 AS Decimal(18 , 2))<0) then CAST(PayMoney*InOrOut * Point / 10 AS Decimal(18 , 2)) end as fget,BargainCompanyName";

        string strSql = "select " + TableFieldList + " from " + TableName + " where " + sb.ToString() + " order by " + strOrderField;

        System.Data.DataSet ds = Chenxin.Lib.Business.DAL.v_Contract_CommisionDetailInfo.GetList(strSql);
        
        string strHtml = GetHTML(ds.Tables[0]);

        ExportByServce(context, strHtml);
    }

    private string GetHTML(System.Data.DataTable dt)
    {
        StringBuilder sb = new StringBuilder();
        sb.Append("<table class=\"tabsorternew table_archive\"><tr>");
        sb.Append("<th style=\"width:120px;\">商圈</th>");
        sb.Append("<th style=\"width:120px;\">门店</th>");
        sb.Append("<th style=\"width:100px;\">业务员</th>");
        sb.Append("<th style=\"width:130px;\">成交编号</th>");
        sb.Append("<th style=\"width:100px;\">成交日期</th>");
        sb.Append("<th style=\"width:270px;\">房屋地址</th>");
        sb.Append("<th style=\"width:100px;\">时间归属</th>");
        sb.Append("<th style=\"width:100px;\">本次实际收/付</th>");
        sb.Append("<th style=\"width:100px;\">点数</th>");
        sb.Append("<th style=\"width:110px;\">分摊实收金额</th>");
        sb.Append("<th style=\"width:100px;\">分摊实付金额</th>");
        sb.Append("<th style=\"width:100px;\">合计</th>");
        sb.Append("<th style=\"width:120px;\">归属公司</th></tr>");

        double point = 0.0;            //点数
        double zget = 0.0;     //分摊应收佣收
        double fget = 0.0;      //分摊折佣
        double sum = 0.0;      //分摊咨询费

        foreach (System.Data.DataRow dr in dt.Rows)
        {
            sb.Append("<tr>");
            sb.AppendFormat("<td>{0}</td>", DatasetHelper.GetValue(dr,"business",""));
            sb.AppendFormat("<td>{0}</td>", DatasetHelper.GetValue(dr,"departname",""));
            sb.AppendFormat("<td>{0}</td>", DatasetHelper.GetValue(dr,"Employee",""));
            sb.AppendFormat("<td>{0}</td>", DatasetHelper.GetValue(dr,"ContractCode",""));
            sb.AppendFormat("<td>{0}</td>", DatasetHelper.GetValue(dr,"ContractDate",""));
            sb.AppendFormat("<td>{0}</td>", DatasetHelper.GetValue(dr,"HouseAddress",""));
            sb.AppendFormat("<td>{0}</td>", DatasetHelper.GetValue(dr, "typetime", "") == "10" ? "本期" : "前期");
            sb.AppendFormat("<td>{0}</td>", DatasetHelper.GetValue(dr,"realget",""));
            sb.AppendFormat("<td>{0}</td>", DatasetHelper.GetValue(dr,"point",""));
            sb.AppendFormat("<td>{0}</td>", DatasetHelper.GetValue(dr,"zget",""));
            sb.AppendFormat("<td>{0}</td>", DatasetHelper.GetValue(dr,"fget",""));
            sb.AppendFormat("<td>{0}</td>", string.IsNullOrEmpty(DatasetHelper.GetValue(dr,"zget","")) != true ? DatasetHelper.GetValue(dr,"zget","") : DatasetHelper.GetValue(dr,"fget",""));
            sb.AppendFormat("<td>{0}</td>", DatasetHelper.GetValue(dr,"BargainCompanyName",""));
            sb.Append("</tr>");

            point += string.IsNullOrEmpty(dr["Point"].ToString()) ? 0 : double.Parse(dr["Point"].ToString());
            zget += string.IsNullOrEmpty(dr["zget"].ToString()) ? 0 : double.Parse(dr["zget"].ToString());
            fget += string.IsNullOrEmpty(dr["fget"].ToString()) ? 0 : double.Parse(dr["fget"].ToString());
            sum += string.IsNullOrEmpty(dr["zget"].ToString()) ? double.Parse(dr["fget"].ToString()) : double.Parse(dr["zget"].ToString());

        }
        sb.AppendFormat("<tr><td>总合计</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td>{0}</td><td>{1}</td><td>{2}</td>",  zget, fget, sum);
        sb.Append("<td></td></tr></table>");

        return sb.ToString();
    }
    
    
    private void GetList(HttpContext context)
    {
        string year = "";
        string month = "";

        if (!string.IsNullOrEmpty(context.Request["ActualDate"]))
        {
            string[] list = context.Request["ActualDate"].Split('-');
            year = list[0];
            month = list[1].TrimStart('0');
        }
        else
        {
            year = DateTime.Now.Year.ToString();
            month = DateTime.Now.Month.ToString();
        }

        //查询条件
        System.Text.StringBuilder sb = new System.Text.StringBuilder();

        if (CurrentUserInfo.ContainsPermit(Constants.Permit_IncomeDetails_Search_All))
        {
            sb.Append(" (1=1) ");
        }
        else if (CurrentUserInfo.ContainsPermit(Constants.Permit_IncomeDetails_Search_City))
        {
            sb.AppendFormat(" (DepartId in (Select DepartId from v_Hr_DepartmentInfo WHERE cityid='{0}')) ", CurrentUserInfo.CityId.ToString());
        }
        else if (CurrentUserInfo.ContainsPermit(Constants.Permit_IncomeDetails_Search_Company))
        {
            sb.AppendFormat(" (DepartId in (select DepartId from v_Hr_DepartmentInfo WHERE companyid='{0}')) ", CurrentUserInfo.CompanyId.ToString());
        }
        else if (CurrentUserInfo.ContainsPermit(Constants.Permit_IncomeDetails_Search_Depart))
        {
            sb.Append(" (EXISTS(");
            sb.AppendFormat("Select 1 from t_Business_EmpSetting M WHERE v_Report_Commission_FinanceSumInfo.DepartId=M.DepartId And M.Userid= '{0}' and SearchIncomeDetails=1", CurrentUserInfo.UserId.ToString());
            sb.Append(")) ");
        }
        else
            sb.AppendFormat(" (EmployeeId = '{0}') ", CurrentUserInfo.UserId);
        
        

        sb.AppendFormat(" and payyear='{0}' and paymonth ='{1}' ",year,month);

        //点数门店；合同所属
        if (context.Request["type"] == "10")
        {
            //4级
            Guid city = (string.IsNullOrEmpty(context.Request["cityid"])) ? Guid.Empty : new Guid(context.Request["cityid"]);
            Guid company = (string.IsNullOrEmpty(context.Request["companyid"])) ? Guid.Empty : new Guid(context.Request["companyid"]);
            Guid businessdistrict = (string.IsNullOrEmpty(context.Request["areaid"])) ? Guid.Empty : new Guid(context.Request["areaid"]);
            Guid departs = (string.IsNullOrEmpty(context.Request["departid"])) ? Guid.Empty : new Guid(context.Request["departid"]);
            Guid userId = (string.IsNullOrEmpty(context.Request["UserId"])) ? Guid.Empty : new Guid(context.Request["UserId"]);

            if (userId != Guid.Empty)
                sb.AppendFormat(" AND (EmployeeId = '{0}') ", userId);
            else if (departs != Guid.Empty)
            {
                sb.AppendFormat(" and DepartId = '{0}'", departs);
            }
            else if (businessdistrict != Guid.Empty)
            {
                Chenxin.Lib.Hr.Entity.Hr_DepartmentInfo item = null;
                string strCachkey = string.Format(Chenxin.Lib.Cache.CacheDataType.GLOBAL_DEPARTMENT, businessdistrict);
                Chenxin.Lib.Cache.CacheService cs = new Chenxin.Lib.Cache.CacheService(Chenxin.Lib.Cache.CacheConn.xCacheConn.GLOBAL);
                item = cs.GetObject<Chenxin.Lib.Hr.Entity.Hr_DepartmentInfo>(strCachkey);
                if (!cs.Hit)
                {
                    item = Chenxin.Lib.Hr.DAL.Hr_DepartmentInfo.GetModel(businessdistrict);
                    if (item != null) cs.Set(strCachkey, item, DateTime.Now.AddDays(1));
                }

                if (item == null)
                {
                    context.Response.Write(retExecResult(Chenxin.Lib.Utility.Constants.ExecResult.Error, "获取商圈或分类数据丢失", ""));
                }
                sb.AppendFormat(" and (DepartId in (Select DepartId from v_Hr_DepartmentInfo where DepartSrc like '{0}%'))", item.DepartSrc);
            }
            else if (company != Guid.Empty)
            {
                sb.AppendFormat(" and (DepartId in (select DepartId from v_Hr_DepartmentInfo WHERE companyid='{0}')) ", company);
            }
            else if (city != Guid.Empty)
            {
                sb.AppendFormat(" and (DepartId in (Select DepartId from v_Hr_DepartmentInfo WHERE cityid='{0}')) ", city);
            }
        }
        else if (context.Request["type"] == "20")
        {
            Guid company = (string.IsNullOrEmpty(context.Request["companyid"])) ? Guid.Empty : new Guid(context.Request["companyid"]);
            if (company != Guid.Empty)
            {
                sb.AppendFormat(" and (BargainCompanyId='{0}') ", company);
            }
        }
        
        //关键字
        if (!string.IsNullOrEmpty(context.Request["Keyword"]))
            sb.AppendFormat(" and (ContractCode like '%{0}%' or HouseAddress like '%{0}%')", context.Request["Keyword"]);
        
        //本期前期
        if (!string.IsNullOrEmpty(context.Request["datebelong"]))
        {
            //本期
            if (context.Request["datebelong"] == "10")
                sb.AppendFormat(" and ArchivementYear='{0}' and ArchivementMonth ='{1}' ", year, month);
            //前期
            else
                sb.AppendFormat(" and ((ArchivementYear='{0}' and ArchivementMonth <'{1}') or (ArchivementYear<'{0}')) ", year, month);
        }


        int strPageIndex = (string.IsNullOrEmpty(context.Request["pageindex"])) ? 1 : int.Parse(context.Request["pageindex"]) + 1;//页码
        //页显示行数
        int strPageSize = (string.IsNullOrEmpty(context.Request["pagesize"])) ? 30 : int.Parse(context.Request["pagesize"]);//页记录数
        //欲选择字段列表 
        string FieldList = "DepartId,business,departname,ContractCode,ContractDate,HouseAddress,Employee,typetime,realget,point,zget,fget,BargainCompanyName";
        
         
        //所要查询的表名
        string TableName = "v_Report_Commission_FinanceSumInfo";
        //排序
        string strOrderField = " DepartId desc";
        //子查询欲选择字段列表
        string TableFieldList = "DepartId,dbo.FN_GetBussinessByDepartID(DepartId) as business,dbo.FN_GetDepartNameByDepartID(DepartId) as departname,"
                    + string.Format("ContractCode,ContractDate,HouseAddress,Employee,(case when (ArchivementYear='{0}' and ArchivementMonth ='{1}') then '10' ", year, month)
                    + string.Format("when ((ArchivementYear='{0}' and ArchivementMonth <'{1}') or (ArchivementYear<'{0}')) then '20' ", year, month)
                    + "end) as typetime,PayMoney*InOrOut as realget,point,"
                    + "case when (CAST(PayMoney*InOrOut * Point / 10 AS Decimal(18 , 2))>=0) then CAST(PayMoney*InOrOut * Point / 10 AS Decimal(18 , 2)) end as zget,"
                    + "case when (CAST(PayMoney*InOrOut * Point / 10 AS Decimal(18 , 2))<0) then CAST(PayMoney*InOrOut * Point / 10 AS Decimal(18 , 2)) end as fget,BargainCompanyName";

        System.Data.DataSet ds = Chenxin.Lib.Business.DAL.v_Contract_CommisionDetailInfo.GetList(strPageIndex, strPageSize, strOrderField, TableName, sb.ToString(), FieldList, TableFieldList,true);

        //*************获取总合计数*****************
        string strSql = "select sum(isnull(point,0)) as point,sum(case when (CAST(PayMoney*InOrOut * Point / 10 AS Decimal(18 , 2))>=0) then CAST(PayMoney*InOrOut * Point / 10 AS Decimal(18 , 2)) end) as zget,"
        + "sum(isnull(case when (CAST(PayMoney*InOrOut * Point / 10 AS Decimal(18 , 2))<0) then CAST(PayMoney*InOrOut * Point / 10 AS Decimal(18 , 2)) end,0)) as fget from " + TableName + " where " + sb.ToString();
        System.Data.DataSet ds_count = Chenxin.Lib.Business.DAL.v_Contract_CommisionDetailInfo.GetList(strSql);
        System.Data.DataRow dr = ds.Tables[0].NewRow();
        dr["business"] = "总合计";
        dr["zget"] = ds_count.Tables[0].Rows[0]["zget"];
        dr["fget"] = ds_count.Tables[0].Rows[0]["fget"];
        ds.Tables[0].Rows.Add(dr);
        //*****************************************
        

        context.Response.Write(string.Format("{{success:1,msg:'',data:{{count:{0},{1}}}}}", ds.Tables[1].Rows[0][0], Chenxin.Lib.Utility.JsonHelp.Convert2Json(ds.Tables[0])));
    }
}